package cn.jhc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.cj.jdbc.MysqlDataSource;



public class DaoRu {
    //单例模式
    private static MysqlDataSource dataSource = null;
    
    private static final String INSERT_SQL = "insert into survey (name, sex, hobbies, origin, dest, ctype) values(?, ?, ?, ?, ?, ?)";
    private static final String SELECT_SQL = "select id, name, sex, hobbies, origin, dest, ctype from survey";
    private static final String REMOVE_SQL = "delete from survey where id = ?";
    private static final String LOGIN_SELECT_SQL = "select id from user where username = ? and password = sha(?)";

    public synchronized static DataSource getDataSource() {
        if( dataSource == null) {
            dataSource = new MysqlDataSource();
            dataSource.setURL("jdbc:mysql://localhost/server_test?user=suuser&password=123456");
        }
        return dataSource;
    }

    public void save(Item item) throws SQLException {
        try (Connection connection = getDataSource().getConnection();
            PreparedStatement stat = connection.prepareStatement(INSERT_SQL)) {
            stat.setString(1, item.getName());
            stat.setString(2, item.getSex());
            stat.setString(3, item.getHobbies());
            stat.setString(4, item.getOriginFilename());
            stat.setString(5, item.getReFilename());
            stat.setString(6, item.getCtype());
            stat.executeUpdate();
        } 
    }

    public List<Item> findAllItems() throws SQLException {
        List<Item> items = new ArrayList<>();
        try(Connection connection = getDataSource().getConnection();
            Statement stat = connection.createStatement();
            ResultSet rs = stat.executeQuery(SELECT_SQL)) {
            while (rs.next()) {
                Item item = new Item();
                item.setId(rs.getInt(1));
                item.setName(rs.getString(2));
                item.setSex(rs.getString(3));
                item.setHobbies(rs.getString(4));
                item.setOriginFilename(rs.getString(5));
                item.setReFilename(rs.getString(6));
                item.setCtype(rs.getString(7));
                items.add(item);
            }
        }
        return items;

    }

    public void remove(int id) throws SQLException {
        try(Connection connection = getDataSource().getConnection();
            PreparedStatement stat = connection.prepareStatement(REMOVE_SQL)) {
                stat.setInt(1, id);
                stat.executeUpdate();
            }
    }

    public boolean login(String username, String password) throws SQLException {
        boolean flag = false;
        try(Connection connection = getDataSource().getConnection();
            PreparedStatement stat = connection.prepareStatement(LOGIN_SELECT_SQL)) {
                stat.setString(1, username);
                stat.setString(2, password);
                
                ResultSet rs = stat.executeQuery();
                // System.out.println(rs.next());
                if (rs.next()) {
                    flag = true;
                }
        }
        return flag;
        
    }
}
